package com.soikc.kuaidiE.dao.imp;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.soikc.kuaidiE.bean.Courier;
import com.soikc.kuaidiE.dao.BaseCourierDao;
import com.soikc.kuaidiE.util.DruidUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class BaseCourierDaoImp implements BaseCourierDao {
    /**
     * 查询所有用户
     *
     * @return
     */
    @Override
    public List<Courier> queryList() {
        final String sql = "SELECT * FROM courieruser";
        Connection connection = DruidUtil.getConnection();
        List<Courier> list = new ArrayList<>();
        try {
            ResultSet resultSet = DruidUtil.query(connection, sql);
            list = Courier.createList(resultSet);
        } catch (SQLException err) {
            err.printStackTrace();
        } finally {
            DruidUtil.close(connection);
        }
        return list;
    }

    /**
     * 分页查询
     *
     * @param offset     开始位置
     * @param pageNumber 查询数量
     * @return
     */
    @Override
    public List<Courier> queryList(int offset, int pageNumber) {
        final String sql = "SELECT * FROM courieruser LIMIT ?,?";
        Connection connection = DruidUtil.getConnection();
        List<Courier> list = new ArrayList<>();
        try {
            ResultSet resultSet = DruidUtil.ppsQuery(connection, sql,offset,pageNumber);
            list = Courier.createList(resultSet);
        } catch (SQLException err) {
            err.printStackTrace();
        } finally {
            DruidUtil.close(connection);
        }
        return list;
    }

    /**
     * 以手机号查找用户
     *
     * @param phone
     * @return
     */
    @Override
    public Courier queryPhone(String phone) {
        final String sql = "SELECT * FROM courieruser WHERE PHONE=?";
        Connection connection = DruidUtil.getConnection();
        try {
            ResultSet resultSet = DruidUtil.ppsQuery(connection, sql,phone);
            if (resultSet.next()) {
                return Courier.create(resultSet);
            }
        } catch (SQLException err) {
            err.printStackTrace();
        } finally {
            DruidUtil.close(connection);
        }
        return null;
    }

    /**
     * 添加一个用户
     *
     * @param courier
     * @return
     */
    @Override
    public boolean add(Courier courier) {
        Connection connection = DruidUtil.getConnection();
        final String sql = "INSERT INTO courieruser () VALUES();";
        List value = new ArrayList();
        List<String> key = new ArrayList();
        JSONObject json = (JSONObject) JSON.toJSON(courier);
        Set<Map.Entry<String, Object>> entries = json.entrySet();
        entries.forEach(ent->{
            Object val = ent.getValue();
            if (val == null) {
                return;
            }
            key.add(ent.getKey());
            value.add(val);
        });
        String[] s = new String[key.size()];
        Arrays.fill(s, "?");
        String s1 = Arrays.toString(s).replaceAll("^\\[|\\]$", "");
        final String[] s2 = {sql.replaceAll("\\(", "(" + s1)};
        key.forEach(tt -> {
            s2[0] = s2[0].replaceFirst("\\?", tt);
        });
        int b=0;
        try {
            b = DruidUtil.ppsUpdate(connection, s2[0],value);
        } catch (SQLException err) {
            err.printStackTrace();
        } finally {
            DruidUtil.close(connection);
        }
        return b>0;
    }

    /**
     * 修改用户
     *
     * @param id
     * @param courier
     * @return
     */
    @Override
    public boolean update(int id, Courier courier) {
        final StringBuffer sql = new StringBuffer("UPDATE courieruser SET ");
        Connection connection = DruidUtil.getConnection();
        String ee = " WHERE ID=?";
        JSONObject json = (JSONObject) JSON.toJSON(courier);
        Set<Map.Entry<String, Object>> entries = json.entrySet();

        List value =new ArrayList();
        entries.forEach(entre->{
            Object val = entre.getValue();
            String key = entre.getKey();
            if (val == null || key.equals("id")) {
                return;
            }
            sql.append(key + "=?,");
            value.add(val);
        });
        String s = sql.toString().replaceFirst(",$", ee);
        value.add(id);
        int b=0;
        try {
            b = DruidUtil.ppsUpdate(connection, s,value);
        } catch (SQLException err) {
            err.printStackTrace();
        } finally {
            DruidUtil.close(connection);
        }
        return b>0;
    }

    /**
     * 以id删除用户
     *
     * @param id
     * @return
     */
    @Override
    public boolean delID(int id) {
        final String sql = "DELETE FROM courieruser WHERE ID=?";
        Connection connection = DruidUtil.getConnection();
        int b=0;
        try {
            b = DruidUtil.ppsUpdate(connection, sql,id);
        } catch (SQLException err) {
            err.printStackTrace();
        } finally {
            DruidUtil.close(connection);
        }
        return b>0;
    }
}
